
package com.edu.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.edu.entity.Student;

public class BaseDao {
		public static final String URL="jdbc:mysql://127.0.0.1:3306/demo?";
		public static final String USER="root";
		public static final String PASS="123456";
		public static final String DRIVER="com.mysql.cj.jdbc.Driver";
	
		/**
		 * 执行 添加 删除 修改的方法
		 * @param String SQL，Object占位符对应的值得数组 没有占位符传入null
		 * @RETURN int 影响行数 -1没有执行
		 * */
		public static int executeUpdate(String sql,Object[] param) {
			Connection conn=null;
			PreparedStatement ps = null;
			try {
				conn = getConn();
				ps = conn.prepareStatement(sql);//预编译sql
				if(param != null) {
					for (int i = 0; i < param.length; i++) {
						ps.setObject(i+1, param[i]);
					}
				}
				int num = ps.executeUpdate();//执行sql
				return num;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
					closeAll(conn, ps);
			}
			return -1;
		}
		
		/**
		 * 执行 添加 删除 修改的方法
		 * @param String SQL
		 * @RETURN int 影响行数
		 * */
		public static int executeUpdate(String sql) {
			Connection conn=null;
			PreparedStatement ps = null;
			try {
				conn = getConn();
				ps = conn.prepareStatement(sql);//预编译sql
				int num = ps.executeUpdate();
				return num;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
					closeAll(conn, ps);
			}
			return -1;
		}
		
		/**
		 * 获得数据库连接
		 * */
		public static Connection getConn() {
			try {
				Class.forName(DRIVER);
				Connection conn=DriverManager.getConnection(URL, USER, PASS);
				if(conn != null) {
					System.out.println("连接成功");
				}else {
					System.out.println("数据库连接失败");
				}
				return conn;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			return null;
		}
		/**
		 * 释放资源Connection，PreparedStatement，ResultSet
		 * */
		public static void closeAll(Connection conn,Statement ps,ResultSet rs) {
			try {
				if(rs !=null) {rs.close();}
				if(ps !=null) {ps.close();}
				if(conn !=null) {conn.close();}
			} catch (SQLException e) {e.printStackTrace();}
		}
		
		/**
		 * 释放资源Connection，PreparedStatement
		 * */
		public static void closeAll(Connection conn,PreparedStatement ps) {
			try {
				if(ps !=null) {ps.close();}
				if(conn !=null) {conn.close();}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		/**
		 * 查询案例
		 * 可借鉴
		 * */
		
	//	public User query(String username, String userpass) {
	//		Connection conn=BaseDao.getConn();
	//		PreparedStatement s = null;
	//		ResultSet rs = null;
	//		try {
	//			if(conn!=null) {
	//				System.out.println("连接成功");
	//			}
	//			String sql = "select * from user where username='"+username+"' and userpass='"+userpass+"'";
	//			System.out.println(sql);
	//			s = conn.prepareStatement(sql);
	//			rs =  s.executeQuery();
	//			while(rs.next()) {
	//				User user = new User();
	//				user.setId(rs.getInt("id"));
	//				user.setUsername(rs.getString("username"));
	//				user.setUserpass(rs.getString("userpass"));
	//				user.setSex(rs.getString("sex"));
	//				user.setAge(rs.getInt("age"));
	//				return user;
	//			}
	//			
	//		} catch (SQLException e) {
	//			// TODO Auto-generated catch block
	//			e.printStackTrace();
	//		}finally {			
	//			BaseDao.closeAll(conn, s,rs);
	//		}
	//		return null;
	//	}
	
		public List<Student> queryAll() {
			Connection conn=BaseDao.getConn();//打开连接
			String sql = "SELECT * FROM student";
			List<Student> list = new ArrayList<Student>();
			PreparedStatement ps= null;
			ResultSet rs=null;
			try {
				ps= conn.prepareStatement(sql);
				rs = ps.executeQuery();			
				while(rs.next()) {
					Student stu=new Student();
					stu.setId(rs.getInt("sid"));
					stu.setName(rs.getString("sname"));
					stu.setAge(rs.getInt("sage"));
					stu.setSex(rs.getString("ssex"));
					list.add(stu);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					BaseDao.closeAll(conn, ps,rs);
				}
				return list;
			}
	}


